package com.hcrazy.manchester.app.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.hcrazy.manchester.app.pojo.BookOrder;
import com.hcrazy.manchester.app.pojo.BookOrderItem;
import com.hcrazy.manchester.app.pojo.PageModel;
import com.hcrazy.manchester.app.utils.Constant;

public class BookOrderDAO extends BaseDAO<BookOrder>{

	public int addBookOrder(BookOrder order) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_book_order(");
		sb.append("orderDate,orderTime,totalPrice,description_mc,userId,status,orderNum,createTime,type");
		sb.append(") values (");
		sb.append(":orderDate,:orderTime,:totalPrice,:description_mc,:userId,:status,:orderNum,:createTime,:type");
		sb.append(")");
		
		update(sb.toString(), order);
		return getIncreamentId();
	}
	
	public void addOrderItems(List<BookOrderItem> items,long orderId) throws Exception{
		if(null != items && !items.isEmpty()){
			StringBuilder sb = new StringBuilder();
			sb.append("insert into him_book_order_item(");
			sb.append("orderId,foodId,amount,description_mc");
			sb.append(") values (");
			sb.append("?,?,?,?");
			sb.append(")");
			List<Object[]> batchArgs = new ArrayList<Object[]>();
			for(BookOrderItem item:items){
				batchArgs.add(new Object[]{item.getOrderId(),item.getFoodId(),item.getAmount(),item.getDescription_mc()});
			}
			//批量添加
			getJdbcTemplate().batchUpdate(sb.toString(), batchArgs);
		}
	}
	
	public List<BookOrderItem> getItemsByOrderId(long orderId) throws Exception{
		String sql = "select a.*,b.foodName,c.menuName,b.price,b.rebate from him_book_order_item a,him_book_food b,him_book_menu c where a.foodId = b.foodId and b.menuId = c.menuId and a.orderId = ?";
		
		return getJdbcTemplate().query(sql, new Object[]{orderId}, new RowMapper<BookOrderItem>(){
			@Override
			public BookOrderItem mapRow(ResultSet rs, int arg1)
					throws SQLException {
				BookOrderItem item = new BookOrderItem();
				item.setAmount(rs.getInt("amount"));
				item.setDescription_mc(rs.getString("description_mc"));
				item.setFoodId(rs.getLong("foodId"));
				item.setItemId(rs.getLong("itemId"));
				item.setOrderId(rs.getLong("orderId"));
				item.setFoodMenu(rs.getString("menuName"));
				item.setFoodName(rs.getString("foodName"));
				item.setPrice(rs.getFloat("price"));
				item.setRebate(rs.getFloat("rebate"));
				return item;
			}
			
		});
	}
	
	public void updateBookOrder(BookOrder order) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_book_order set ");
		sb.append(" status=:status");
		sb.append(" where orderId = :orderId");
		
		update(sb.toString(), order);
	}
	
	public List<BookOrder> listAllInfos(int type) throws Exception{
		String sql = "select * from him_book_order where type = ?";
		return getList(sql, BookOrder.class,type);
	}
	
	public List<BookOrder> listAllInfos(long userId,int type) throws Exception{
		String sql = "select * from him_book_order where userId = ? and type = ?";
		return getList(sql, BookOrder.class,userId,type);
	}
	
	public BookOrder getById(long orderId) throws Exception{
		String sql = "select * from him_book_order where orderId = ?";
		return this.getJavaBean(sql, BookOrder.class, orderId);
	}
	
	public void delBookOrder(long orderId) throws Exception{
		String sql = "delete from him_book_order where orderId = ?";
		this.commonUpdate(sql, orderId);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,long sellerUserId,long sellerId,String start,String end,int status,int type) throws Exception{
		StringBuilder querySQL = new StringBuilder(" select * from (select a.*,b.sellerName,c.`name`,b.userId as sellerUserId from him_book_order a,him_book_seller b,him_book_customer c where a.sellerId = b.sellerId and a.userId = c.userId) m ");
		StringBuilder countSQL = new StringBuilder(" select count(*) from (select a.*,b.sellerName,c.`name`,b.userId as sellerUserId from him_book_order a,him_book_seller b,him_book_customer c where a.sellerId = b.sellerId and a.userId = c.userId) m ");
		
		StringBuilder whereSQL = new StringBuilder(" where type = ? and (name like ? or orderNum like ? ) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(type);
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");

		if(type == Constant.TYPE_BOOK_TICKET){
			querySQL = new StringBuilder(" select * from (select a.*,c.`name` from him_book_order a,him_book_customer c where a.userId = c.userId) m ");
			countSQL = new StringBuilder(" select count(*) from (select a.*,c.`name` from him_book_order a,him_book_customer c where a.userId = c.userId) m ");
		}else{
			if(sellerUserId > 0){
				whereSQL.append(" and sellerUserId = ? ");
				paramList.add(sellerUserId);
			}
			
			if(sellerId > 0){
				whereSQL.append(" and sellerId = ? ");
				paramList.add(sellerId);
			}
		}
				
		if(null != start && null != end && (!"".equals(start) && !"".equals(end))){
			whereSQL.append(" and (orderDate >= ? and orderDate < ?) ");
			paramList.add(start);
			paramList.add(end);
		}
		
		if(status > -1 ){
			whereSQL.append(" and status = ? ");
			paramList.add(status);
		}
		
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, BookOrder.class);
	}
	
	public PageModel getUserPagedInfos(PageModel model,String search,long userId,long sellerId,String start,String end,int status,int type) throws Exception{
		StringBuilder querySQL = new StringBuilder(" select * from (select a.*,b.sellerName,c.`name`,b.userId as sellerUserId from him_book_order a,him_book_seller b,him_book_customer c where a.sellerId = b.sellerId and a.userId = c.userId) m ");
		StringBuilder countSQL = new StringBuilder(" select count(*) from (select a.*,b.sellerName,c.`name`,b.userId as sellerUserId from him_book_order a,him_book_seller b,him_book_customer c where a.sellerId = b.sellerId and a.userId = c.userId) m ");
		
		StringBuilder whereSQL = new StringBuilder(" where type = ? and (name like ? or orderNum like ? ) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(type);
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");

		if(type == Constant.TYPE_BOOK_TICKET){
			querySQL = new StringBuilder(" select * from (select a.*,c.`name` from him_book_order a,him_book_customer c where a.userId = c.userId) m ");
			countSQL = new StringBuilder(" select count(*) from (select a.*,c.`name` from him_book_order a,him_book_customer c where a.userId = c.userId) m ");
		}else{
			if(sellerId > 0){
				whereSQL.append(" and sellerId = ? ");
				paramList.add(sellerId);
			}
			
		}
		
		if(userId > 0){
			whereSQL.append(" and userId = ? ");
			paramList.add(userId);
		}
		
		if(null != start && null != end && (!"".equals(start) && !"".equals(end))){
			whereSQL.append(" and (orderDate >= ? and orderDate < ?) ");
			paramList.add(start);
			paramList.add(end);
		}
		
		if(status > -1 ){
			whereSQL.append(" and status = ? ");
			paramList.add(status);
		}
		
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, BookOrder.class);
	}
}
